package cn.work.dao;

import cn.work.bean.reservationBean;
import cn.work.bean.servicebean;
import cn.work.util.DataBaseutil;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class serviceDao {
    // 插入服务数据到数据库
    public boolean insert(servicebean service) throws SQLException {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DataBaseutil.getConnection(); // 获取数据库连接
            String sql = "INSERT INTO service (name,classname,image, doctor, time, price, introduction, status) VALUES (?,?, ?, ?,?, ?, ?, ?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, service.getName());
            pstmt.setString(2, service.getClassname());
            pstmt.setString(2, service.getImage());
            pstmt.setString(3, service.getDoctor());
            pstmt.setString(4, service.getTime());
            pstmt.setDouble(5, service.getPrice());
            pstmt.setString(6, service.getIntroduction());
            pstmt.setString(7, service.getStatus());

            int rowsAffected = pstmt.executeUpdate();
            return rowsAffected > 0;
        } finally {
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
    }

    //    获取数据库数据
    public List<servicebean> getAllServices() throws SQLException {
        List<servicebean> list = new ArrayList<>();
        Connection connection = DataBaseutil.getConnection();
        if (connection != null) {
            System.out.println("数据库连接成功获取！");
            try {
                Statement statement = connection.createStatement();
                String sql = """
                SELECT *FROM service ;
               /*LEFT JOIN appointment a ON s.id=a.id*/
            """;
                ResultSet rs = statement.executeQuery(sql);
                System.out.println("开始查询数据...");
                if (!rs.isBeforeFirst()) { // 检查是否有结果集
                    System.out.println("SQL 查询结果为空！");
                    return list; // 返回空列表
                } else {
                    System.out.println("SQL 查询有结果！");
                }
                while (rs.next()) {
                    System.out.println("正在封装对象...");
                    servicebean ser = new servicebean();
                    ser.setId(rs.getInt("id"));
                    ser.setName(rs.getString("name"));
                    ser.setClassname(rs.getString("classname"));
                    ser.setImage(rs.getString("image"));
                    ser.setDoctor(rs.getString("doctor"));
                    ser.setTime(rs.getString("time"));
                    ser.setPrice(rs.getDouble("price"));
                    ser.setIntroduction(rs.getString("introduction"));
                    ser.setStatus(rs.getString("status"));
                    list.add(ser);
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        } else {
            System.out.println("数据库连接获取失败！");
        }
        System.out.println("返回的预约列表：");
        for (servicebean ser : list) {
            System.out.println(ser);
        }
        return list;
    }

    //   查询数据
    public List<servicebean> search(String keyword) throws SQLException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<servicebean> serviceList = new ArrayList<>();
        System.out.println("测试");
        try {
            conn = DataBaseutil.getConnection();  // 获取数据库连接
            // 使用 LIKE 进行模糊查询

            String sql = "SELECT * FROM service WHERE name LIKE ? ";

            pstmt = conn.prepareStatement(sql);
            String searchKeyword = "%" + keyword + "%";  // 模糊查询时，加上百分号
            pstmt.setString(1, searchKeyword);  // 设置 name 字段的搜索条件

            System.out.println("Executing SQL: " + sql + " with keyword: " + searchKeyword);
            rs = pstmt.executeQuery();  // 执行查询

            while (rs.next()) {
                // 从 ResultSet 中获取数据并将其转化为 servicebean 对象
                servicebean service = new servicebean();
                service.setId(rs.getInt("id"));
                service.setName(rs.getString("name"));
                service.setClassname(rs.getString("classname"));
                service.setImage(rs.getString("image"));
                service.setDoctor(rs.getString("doctor"));
                service.setTime(rs.getString("time"));
                service.setPrice(rs.getDouble("price"));
                service.setIntroduction(rs.getString("introduction"));
                service.setStatus(rs.getString("status"));

                serviceList.add(service);  // 将结果添加到列表
            }
        } finally {
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        }

        return serviceList;
    }
    // 批量删除服务
    public boolean deleteSelected(List<Integer> serviceIds, HttpServletResponse response) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        if (serviceIds == null || serviceIds.isEmpty()) {
            try {
                response.getWriter().write("{\"success\": false, \"message\": \"没有选中任何服务\"}");
            } catch (IOException e) {
                e.printStackTrace();
            }
            return false;
        }

        try {
            conn = DataBaseutil.getConnection();  // 获取数据库连接

            // 创建 SQL 查询语句，用 IN 语句删除多个记录
            StringBuilder sql = new StringBuilder("DELETE FROM service WHERE id IN (");
            for (int i = 0; i < serviceIds.size(); i++) {
                sql.append("?");
                if (i < serviceIds.size() - 1) {
                    sql.append(",");
                }
            }
            sql.append(")");
            // 打印生成的 SQL 语句
            System.out.println("执行的 SQL: " + sql.toString());
            pstmt = conn.prepareStatement(sql.toString());

            // 设置参数
            for (int i = 0; i < serviceIds.size(); i++) {
                pstmt.setInt(i + 1, serviceIds.get(i));
            }

            // 执行删除操作
            int rowsDeleted = pstmt.executeUpdate();

            if (rowsDeleted > 0) {
                response.getWriter().write("{\"success\": true, \"message\": \"删除成功\"}");
            } else {
                response.getWriter().write("{\"success\": false, \"message\": \"删除失败，未找到相关服务\"}");
            }

            return rowsDeleted > 0;
        } catch (SQLException | IOException e) {
            e.printStackTrace();
            try {
                response.getWriter().write("{\"success\": false, \"message\": \"删除失败，发生了数据库错误\"}");
            } catch (IOException ex) {
                ex.printStackTrace();
            }
            return false;
        } finally {
            // 关闭资源
            try {
                if (pstmt != null) pstmt.close();
                if (conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    // 删除服务方法
    public boolean deleteServiceById(int serviceId) {
        String sql = "DELETE FROM service WHERE id = ?";
        try (Connection conn = DataBaseutil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            // 打印出连接数据库和即将执行的 SQL 语句
            System.out.println("Attempting to delete service with ID: " + serviceId);
            System.out.println("SQL: " + sql);

            // 设置 SQL 语句中的参数
            stmt.setInt(1, serviceId);

            // 执行删除操作
            int rowsAffected = stmt.executeUpdate();
            System.out.println("Rows affected: " + rowsAffected);

            // 根据受影响的行数判断删除是否成功
            return rowsAffected > 0;  // 如果删除成功，返回true
        } catch (SQLException e) {
            System.err.println("Error occurred while deleting service with ID: " + serviceId);
            e.printStackTrace();
            return false;
        }
    }
//    编辑修改数据

    public void update(servicebean ser) throws SQLException {
        String sql = "UPDATE service SET name = ?,classname = ?, doctor = ?, time = ?, price = ?, introduction = ?, status = ?, image = ? WHERE id = ?";
        try (Connection conn = DataBaseutil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, ser.getName());
            ps.setString(2, ser.getDoctor());
            ps.setString(3, ser.getTime());
            ps.setDouble(4, ser.getPrice());
            ps.setString(5, ser.getIntroduction());
            ps.setString(6, ser.getStatus());
            ps.setString(7, ser.getImage());
            ps.setInt(8, ser.getId());

        }
    }

    public servicebean getServiceById(int serviceId) throws SQLException {
        String sql = "SELECT * FROM service WHERE id = ?";
        try (Connection conn = DataBaseutil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, serviceId);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                servicebean service = new servicebean();
                service.setId(rs.getInt("id"));
                service.setName(rs.getString("name"));
                service.setClassname(rs.getString("classname"));
                service.setImage(rs.getString("image"));
                service.setDoctor(rs.getString("doctor"));
                service.setTime(rs.getString("time"));
                service.setPrice(rs.getDouble("price"));
                service.setIntroduction(rs.getString("introduction"));
                service.setStatus(rs.getString("status"));
                return service;
            }
        }
        return null; // 如果没有找到，返回 null
    }

}

